package com.apes.hr.base.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.query.SqlService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.*;

/**
 * 功能：Hr查询Dao
 *
 * @Auther: linwz
 * @Date: 2018-11-20 18:22
 */
@Component
public class HrDao extends ApesDao {

    @Autowired
    SqlService sqlService;

    /**
     * 查询年龄占比
     * @param branch
     * @return
     */
    public List querySex(JSONArray branch) {
        String sSQL = "SELECT  (SELECT DESCRIPTION FROM MAS_ENUMERATION WHERE ID =T.SEX) SEX,\n" +
                "                    COUNT(CASE\n" +
                "                              WHEN T.ID IS NOT NULL THEN\n" +
                "                               1\n" +
                "                              ELSE\n" +
                "                                NULL\n" +
                "                            END) NUM\n" +
                "                 FROM HR_EMPLOYEE T\n" +
                "                 WHERE T.STATE != 'delete'\n" +
                "                   AND T.EMPLOYEE_STATE_ID = '000088'\n" +
                "AND T.WORK_BRANCH_ID IN (:BRANCH_ID)" +
                "                GROUP BY T.SEX";
        JSONObject param = new JSONObject();
        String sSQL2 = "SELECT t.id As ID from mas_branch t";
        List<Map> list = this.find(sSQL2);
        List<Object> stringList = new ArrayList<>();
        list.forEach(lis->{
            stringList.add(lis.get("ID"));
        });
        if(branch.size()>0){
            param.put("BRANCH_ID",branch);
        } else {
            param.put("BRANCH_ID",stringList);
        }
        return this.find(sSQL,param);
    }

    /**
     * 查询全日制与兼职
     * @param
     * @return
     */
    public JSONArray queryJobClass(JSONArray branch) {
        String sSQL = "SELECT COUNT(CASE\n" +
                "               WHEN T.JOB_ID = '0014' OR T.JOB_ID = '0013' THEN\n" +
                "                1\n" +
                "               ELSE\n" +
                "                NULL\n" +
                "             END) AS PARTTIME,\n" +
                "       COUNT(CASE\n" +
                "               WHEN T.JOB_ID != '0014' AND T.JOB_ID != '0013' THEN\n" +
                "                1\n" +
                "               ELSE\n" +
                "                NULL\n" +
                "             END) AS JOB\n" +
                "  FROM HR_EMPLOYEE T\n" +
                " WHERE T.STATE != 'delete'\n" +
                "   AND T.EMPLOYEE_STATE_ID = '000088' AND T.WORK_BRANCH_ID IN (:BRANCH_ID)";
        JSONObject param = new JSONObject();
        String sSQL2 = "SELECT t.id As ID from mas_branch t";
        List<Map> list = this.find(sSQL2);
        List<Object> stringList = new ArrayList<>();
        list.forEach(lis->{
            stringList.add(lis.get("ID"));
        });
        if(branch.size()>0){
            param.put("BRANCH_ID",branch);
        } else {
            param.put("BRANCH_ID",stringList);
        }
        return toJSONArray(this.find(sSQL,param));
    }

    /**
     * 查询年龄占比
     * @param
     * @return
     */
    public JSONArray queryYear(JSONArray branch) {
        String sSQL = "\n" +
                "SELECT COUNT(CASE\n" +
                "               WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM T.BIRTHDAY) >= 16 AND\n" +
                "                    EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM T.BIRTHDAY) < 40 THEN\n" +
                "                1\n" +
                "               ELSE\n" +
                "                NULL\n" +
                "             END) AS Youth,\n" +
                "       COUNT(CASE\n" +
                "               WHEN(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM T.BIRTHDAY) <= 60 AND\n" +
                "       T.SEX = '000150'AND t.employee_state_id ='000088'AND  EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM T.BIRTHDAY) >= 40)\n" +
                "    OR (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM T.BIRTHDAY) <= 50 AND\n" +
                "       T.SEX = '000149' AND t.employee_state_id ='000088'AND  EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM T.BIRTHDAY) >= 40) THEN\n" +
                "                1\n" +
                "               ELSE\n" +
                "                NULL\n" +
                "             END) AS wrinkly,\n" +
                "         COUNT(CASE\n" +
                "               WHEN\n" +
                "                    (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM T.BIRTHDAY) > 60 AND\n" +
                "                     T.SEX = '000150') OR  (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM T.BIRTHDAY) > 50 AND\n" +
                "                     T.SEX = '000149') THEN\n" +
                "                1\n" +
                "               ELSE\n" +
                "                NULL\n" +
                "             END) AS retire      \n" +
                "             \n" +
                "  FROM HR_EMPLOYEE T\n" +
                " WHERE T.STATE != 'delete'\n" +
                "   AND T.EMPLOYEE_STATE_ID = '000088' AND T.WORK_BRANCH_ID IN (:BRANCH_ID)";
        JSONObject param = new JSONObject();
        String sSQL2 = "SELECT t.id As ID from mas_branch t";
        List<Map> list = this.find(sSQL2);
        List<Object> stringList = new ArrayList<>();
        list.forEach(lis->{
            stringList.add(lis.get("ID"));
        });
        if(branch.size()>0){
            param.put("BRANCH_ID",branch);
        } else {
            param.put("BRANCH_ID",stringList);
        }
        return toJSONArray(this.find(sSQL,param));
    }

    /**
     * 学历占比
     * @param
     * @return
     */
    public List<Map> queryEducation(JSONArray branch) {
        String sSQL ="  SELECT NVL((SELECT DESCRIPTION FROM MAS_ENUMERATION WHERE ID = T.DEGREE_ID),'其他') DEGREE_ID,\n" +
                "        COUNT(CASE\n" +
                "                WHEN T.ID IS NOT NULL THEN\n" +
                "                 1\n" +
                "                ELSE\n" +
                "                 NULL\n" +
                "              END) NUM\n" +
                "   FROM HR_EMPLOYEE_EDUCATION T\n" +
                "  WHERE T.EMPLOYEE_ID IN\n" +
                "        (SELECT T.ID\n" +
                "           FROM HR_EMPLOYEE T\n" +
                "          WHERE T.STATE != 'delete'\n" +
                "            AND T.EMPLOYEE_STATE_ID = '000088' AND T.WORK_BRANCH_ID IN (:BRANCH_ID)) " +
                "  GROUP BY T.DEGREE_ID";
        JSONObject param = new JSONObject();
        String sSQL2 = "SELECT t.id As ID from mas_branch t";
        List<Map> list = this.find(sSQL2);
        List<Object> stringList = new ArrayList<>();
        list.forEach(lis->{
            stringList.add(lis.get("ID"));
        });
        if(branch.size()>0){
            param.put("BRANCH_ID",branch);
        } else {
            param.put("BRANCH_ID",stringList);
        }
        return this.find(sSQL,param);
    }

    /**
     * 人员类型
     * @param
     * @return
     */
    public List<Map> queryPostGroup(JSONArray branch){
        String sSQL ="SELECT NVL((SELECT T.NAME FROM HR_WORK_POST_GROUP T WHERE ID = A.POST_GROUP_ID),'其他') POST_GROUP_ID,\n" +
                "       COUNT(T.ID) AS RETIRE\n" +
                "  FROM HR_EMPLOYEE T, HR_WORK_POST A\n" +
                " WHERE T.STATE != 'delete'\n" +
                "   AND T.EMPLOYEE_STATE_ID = '000088'\n" +
                "   AND T.POST_ID = A.ID" +
                " AND T.WORK_BRANCH_ID IN (:BRANCH_ID)" +
                " GROUP BY A.POST_GROUP_ID";
        JSONObject param = new JSONObject();
        String sSQL2 = "SELECT t.id As ID from mas_branch t";
        List<Map> list = this.find(sSQL2);
        List<Object> stringList = new ArrayList<>();
        list.forEach(lis->{
            stringList.add(lis.get("ID"));
        });
        if(branch.size()>0){
            param.put("BRANCH_ID",branch);
        } else {
            param.put("BRANCH_ID",stringList);
        }
        return this.find(sSQL,param);
    }

    public List<Map> findByPostAndTransferOnAndTransferOutNew(String rewardDate,Date transferOn , Date transferOut, List<String> postList){
        String sSQL ="SELECT T.EMPLOYEE_ID ,T.WORK_BRANCH_ID,T.POST_ID," +
                "T.ON_DUTY_STATE,T.TRANSFER_ON,T.TRANSFER_OUT,T.TRANSFER_APPLICATION_ITEM_ID," +
                "       (SELECT SUM(a.value)" +
                "         FROM HR_REWARD_PUNISH c, HR_REWARD_PUNISH_ITEM A" +
                "        WHERE c.ID = A.REWARD_PUNISH_ID" +
                "          AND c.REWARD_DATE >=  ADD_MONTHS(to_date(:rewardDate,'yyyy-mm-dd'),-12)" +
                "          AND c.STATE = 'sendOut'" +
                "          AND A.EMPLOYEE_ID = t.employee_id) valueTotal," +
                "        GET_ENUMERATION_NAME(T.ON_DUTY_STATE, 'onDutyState')" +
                "        ON_DUTY_STATE_DESCRIPTION," +
                "       " +
                "        (SELECT DESCRIPTION" +
                "                 FROM MAS_ENUMERATION" +
                "                WHERE ID = ITEM.TRANSFER_KIND) TRANSFER_KIND" +
                "        FROM HR_TRANSFER_RECORD T," +
                "        HR_TRANSFER_APPLICATION_ITEM ITEM" +
                "        WHERE ITEM.ID = T.TRANSFER_APPLICATION_ITEM_ID" +
                "         AND T.VALID = 1" +
                "         AND T.TRANSFER_ON <= :transferOut" +
                "         AND (T.TRANSFER_OUT >= :transferOn OR T.TRANSFER_OUT IS NULL)" +
                "         AND T.POST_ID IN (:postList)";
        JSONObject param = new JSONObject();
        param.put("rewardDate",rewardDate);
        param.put("transferOn",transferOn);
        param.put("transferOut",transferOut);
        param.put("postList",postList);
        return this.find(sSQL,param);
    }


      /**
     * 查询排班信息
     * @param
     * @return
     */
    public List<Map> queryPivot(String dateList,String deptID,String employeeID,String postId) {
        String sSQL = " select * from  (SELECT t.company_id,t.company_name,t.dept_id,t.dept_name,t.post_id," +
                " t.post_name,t.employee_id,t.employee_name,t.valid," +
                " to_char(  t.schedule_date_time,'yyyy-mm-dd') AS schedule_date_time  FROM hr_schedule t " +
                " where t.employee_id in (select h.id from hr_employee h where h.employee_state_id = '000088'" +
                " and h.company_id = t.company_id and t.dept_id = h.work_branch_id and t.post_id = h.post_id)" +
                " and t.dept_name = (select b.name from mas_branch b WHERE b.id = t.dept_id) " +
                " and t.company_name = (select p.name FROM mas_party_role r,mas_party p WHERE r.id = t.company_id and r.party_id = p.id )" +
                " and t.post_name = (select p.name from hr_work_post p WHERE p.id = t.post_id))" +
                "  pivot( max(valid) for  schedule_date_time  in ("+ dateList +") ) where 1 = 1 ";
        JSONObject param = new JSONObject();
        if (deptID == null || deptID.isEmpty()){
        }else{
            sSQL= sSQL + "and dept_name like '%"+ deptID +"%'";
        }
        if (employeeID == null || employeeID.isEmpty()) {
        }else {
            sSQL = sSQL + "and employee_name like '%"+ employeeID +"%'";
        }
        if (postId == null || postId.isEmpty()){
        }else{
            sSQL= sSQL + "and post_name like '%"+ postId +"%'";
        }
        return this.find(sSQL,param);
    }


    /**
     * 查询打卡详情
     * @param
     * @return
     */
    public List<Map> queryHrAttendanceItem(List<String> dateList,String deptID,String employeeID,String postId) {
        String sSQL = " select t.company_id,t.company_name,t.dept_id,t.dept_name,t.post_id," +
                " t.post_name,t.employee_id,t.employee_name,t.group_id,t.group_name,t.plan_id," +
                " t.plan_name,t.work_date,t.on_Duty_user_check_time,t.off_Duty_user_check_time," +
                " t.on_Duty_time_result,t.off_Duty_time_result,t.on_Duty_user_address,t.off_Duty_user_address," +
                " t.on_Duty_location_result,t.off_Duty_location_result,t.on_Duty_outside_remark,t.off_Duty_outside_remark" +
                " from  hr_attendance_item t where 1 = 1 ";
        JSONObject param = new JSONObject();
        if (dateList==null || dateList.isEmpty()){}else {
            sSQL= sSQL + "and t.work_date >= to_date("+ dateList.get(0) +",'yyyy-mm-dd') AND t.work_date <= to_date("+ dateList.get(1) +",'yyyy-mm-dd')";
        }
        if (deptID == null || deptID.isEmpty()){
        }else{
            sSQL= sSQL + "and dept_name like '%"+ deptID +"%'";
        }
        if (employeeID == null || employeeID.isEmpty()) {
        }else {
            sSQL = sSQL + "and employee_name like '%"+ employeeID +"%'";
        }
        if (postId == null || postId.isEmpty()){
        }else{
            sSQL= sSQL + "and post_name like '%"+ postId +"%'";
        }
        return this.find(sSQL,param);
    }


    /**
     * 查询人员档案（奖惩使用）
     * @param
     * @return
     */
    public String findEmployee(JSONObject param) {
        //
        // 查询人员档案（在职）
        String sSQL = "   SELECT A.ID,A.WORK_BRANCH_ID, A.NAME, A.POST_ID, A.PARTY_ID,A.ENTRY_DATE, A.company_id," +
                "           A.ON_DUTY_STATE," +
                "   (SELECT C.NAME FROM MAS_PARTY_ROLE B , MAS_PARTY C WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME,\n" +
                "    (select E.DESCRIPTION from mas_enumeration e where a.on_duty_state = e.id) on_duty_state_name,\n" +
                "   (SELECT E.NAME FROM MAS_BRANCH E WHERE A.WORK_BRANCH_ID = E.ID) WORK_BRANCH_NAME,\n" +
                "   (SELECT E.NAME FROM HR_WORK_POST E WHERE A.POST_ID = E.ID) POST_NAME,\n" +
                "   (SELECT E.DESCRIPTION FROM MAS_PARTY_ROLE B, MAS_PARTY C, MAS_CORPORATE D, MAS_ENUMERATION E WHERE A.COMPANY_ID = B.ID\n" +
                "      AND B.PARTY_ID = C.ID\n" +
                "      AND C.ID = D.PARTY_ID \n" +
                "      AND D.OPERATION = E.ID) OPERATION\n" +
                "     FROM HR_EMPLOYEE A " +
                "     WHERE 1 = 1 " +
                "    AND A.EMPLOYEE_STATE_ID = '000088' " ; // AND A.EMPLOYEE_STATE_ID = '000088'    000088 在职

        // 运营模式条件
        if (param.getString("operation") != null )
            sSQL += " AND EXISTS (SELECT D.OPERATION FROM  MAS_PARTY_ROLE B, MAS_PARTY C, MAS_CORPORATE D     WHERE A.COMPANY_ID = B.ID\n" +
                    "      AND B.PARTY_ID = C.ID\n" +
                    "      AND C.ID = D.PARTY_ID\n" +
                    "      AND D.OPERATION in :operation )";
        if(param.getString("workBranch") != null )
            sSQL += " AND A.WORK_BRANCH_ID in :workBranch";
        if(param.getString("post") != null )
            sSQL += " AND A.POST_ID in :post";
        if(param.getString("name") != null )
            sSQL += " AND A.name = :name ";
        // 离职人员
        sSQL += " UNION " +
                "  SELECT A.ID, A.WORK_BRANCH_ID, A.NAME, A.POST_ID, A.PARTY_ID, A.ENTRY_DATE,\n" +
                "       A.COMPANY_ID, A.ON_DUTY_STATE,\n" +
                "       (SELECT C.NAME\n" +
                "           FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                "          WHERE A.COMPANY_ID = B.ID\n" +
                "            AND B.PARTY_ID = C.ID) COMPANY_NAME,\n" +
                "       (SELECT E.DESCRIPTION\n" +
                "           FROM MAS_ENUMERATION E\n" +
                "          WHERE A.ON_DUTY_STATE = E.ID) ON_DUTY_STATE_NAME,\n" +
                "       (SELECT E.NAME FROM MAS_BRANCH E WHERE A.WORK_BRANCH_ID = E.ID) WORK_BRANCH_NAME,\n" +
                "       (SELECT E.NAME FROM HR_WORK_POST E WHERE A.POST_ID = E.ID) POST_NAME,\n" +
                "       (SELECT E.DESCRIPTION\n" +
                "           FROM MAS_PARTY_ROLE B, MAS_PARTY C, MAS_CORPORATE D,\n" +
                "                MAS_ENUMERATION E\n" +
                "          WHERE A.COMPANY_ID = B.ID\n" +
                "            AND B.PARTY_ID = C.ID\n" +
                "            AND C.ID = D.PARTY_ID\n" +
                "            AND D.OPERATION = E.ID) OPERATION\n" +
                "  FROM HR_QUIT_JOB_ORDER B, HR_EMPLOYEE A \n" +
                " WHERE 1 = 1\n" +
                "AND B.EMPLOYEE_ID = A.ID\n" +
                "AND B.STATE = 'confirm' \n" +
                "AND TO_DATE(TO_CHAR(B.PLAN_LAST_DAY,'YYYY-MM-DD'), 'YYYY-MM-DD') >= to_date('" +param.getString("effectiveDate") + "','YYYY-MM-DD')" +
                "AND TO_DATE(TO_CHAR(B.PLAN_LAST_DAY,'YYYY-MM-DD'), 'YYYY-MM-DD') <=  to_date('"+param.getString("endDate") + "','YYYY-MM-DD')" ;
                // 运营模式条件
                if (param.getString("operation") != null )
                    sSQL += " AND EXISTS (SELECT D.OPERATION FROM  MAS_PARTY_ROLE B, MAS_PARTY C, MAS_CORPORATE D     WHERE A.COMPANY_ID = B.ID\n" +
                            "      AND B.PARTY_ID = C.ID\n" +
                            "      AND C.ID = D.PARTY_ID\n" +
                            "      AND D.OPERATION in :operation )";
                if(param.getString("workBranch") != null )
                    sSQL += " AND A.WORK_BRANCH_ID in :workBranch";
                if(param.getString("post") != null )
                    sSQL += " AND A.POST_ID in :post";
        if (param.getString("workBranch") != null || param.getString("post") != null) {
            sSQL += "  UNION " +
                    " SELECT A.ID,A.WORK_BRANCH_ID, A.NAME, A.POST_ID, A.PARTY_ID,A.ENTRY_DATE, A.COMPANY_ID,\n" +
                    "                           A.ON_DUTY_STATE,\n" +
                    "                   (SELECT C.NAME FROM MAS_PARTY_ROLE B , MAS_PARTY C WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME,\n" +
                    "                    (SELECT E.DESCRIPTION FROM MAS_ENUMERATION E WHERE A.ON_DUTY_STATE = E.ID) ON_DUTY_STATE_NAME,\n" +
                    "                   (SELECT E.NAME FROM MAS_BRANCH E WHERE A.WORK_BRANCH_ID = E.ID) WORK_BRANCH_NAME,\n" +
                    "                   (SELECT E.NAME FROM HR_WORK_POST E WHERE A.POST_ID = E.ID) POST_NAME,\n" +
                    "                   (SELECT E.DESCRIPTION FROM MAS_PARTY_ROLE B, MAS_PARTY C, MAS_CORPORATE D, MAS_ENUMERATION E WHERE A.COMPANY_ID = B.ID\n" +
                    "                      AND B.PARTY_ID = C.ID\n" +
                    "                      AND C.ID = D.PARTY_ID \n" +
                    "                      AND D.OPERATION = E.ID) OPERATION\n" +
                    "FROM HR_TRANSFER_APPLICATION_ITEM B,HR_EMPLOYEE A\n" +
                    "WHERE 1=1  " ;
            if(param.getString("workBranch") != null)
                sSQL +=  " and B.BRANCH_ID_AFTER in :workBranch\n" ;
            if(param.getString("post") != null)
                sSQL += " AND B.POST_ID_AFTER in :post\n" ;
            sSQL += " AND B.TRANSFER_UID = A.ID\n" ;
            sSQL += " AND B.REPEAL = 0\n" +
                    "AND TO_DATE(TO_CHAR(B.EFFECTIVE_DATE,'YYYY-MM-DD'), 'YYYY-MM-DD') >= to_date('" +param.getString("effectiveDate") + "','YYYY-MM-DD')" +
                    "AND TO_DATE(TO_CHAR(B.END_DATE,'YYYY-MM-DD'), 'YYYY-MM-DD') <=  to_date('"+param.getString("endDate") + "','YYYY-MM-DD')" ;
        }
        return sSQL;
    }

    public List<Map> findEmployeeTotal(JSONObject param) {
        String sSQL = "select count(1) total from ( " +findEmployee(param) + ") a";
        return this.find(sSQL,param);
    }



    public List<Map> findEmployeePage(JSONObject param) {
        String sSQL = "SELECT G.*\n" +
                "                  FROM (SELECT G.*, ROWNUM RN, CEIL(TOTAL / :size) PAGE\n" +
                "                           FROM (SELECT MAX(ROWNUM) OVER() TOTAL, G.*\n" +
                "                                    FROM ( "+findEmployee(param) + " )\n" +
                "                                   G ) G\n" +
                "                          WHERE ROWNUM <= DECODE(0, :page, :size, (:page + 1) * :size)) G\n" +
                "                 WHERE RN > (:page) * :size";
                findEmployee(param);
        return this.find(sSQL,param);
    }
}
